package com.monitoring.management.core.service.impl;


import com.monitoring.management.common.exceptions.UserIsExistedException;
import com.monitoring.management.common.exceptions.UserNotExistedException;
import com.monitoring.management.common.exceptions.UserPasswordException;
import com.monitoring.management.common.page.Page;
import com.monitoring.management.common.utils.UUIDUtils;
import com.monitoring.management.common.utils.ency.MD5EncryptUtil;
import com.monitoring.management.core.command.query.QueryUserCommand;
import com.monitoring.management.core.dto.DTowerDTO;
import com.monitoring.management.core.dto.UserDTO;
import com.monitoring.management.core.service.IUserService;
import com.monitoring.management.mappers.DTowerMapper;
import com.monitoring.management.mappers.UserMapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.inject.Inject;
import javax.inject.Named;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by liujunnan on 2017/6/1.
 */
@Named
public class UserServiceImpl implements IUserService {
    static SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static short[] yyyyMMdd = {14, 31, 57, 58, 179, 184, 185, 186, 187, 188};
    static short[] HHmmss = {20, 32, 190, 191, 192};
    static List<short[]> yyyyMMddList = Arrays.asList(yyyyMMdd);
    static List<short[]> hhMMssList = Arrays.asList(HHmmss);
    private static int errorNum = 0;
    static List<String> errorList = new ArrayList<String>();
    @Inject
    private UserMapper userMapper;

    @Inject
    private DTowerMapper dtowerMapper;


    public UserDTO findByName(String userName) {
        UserDTO userDTO = userMapper.selectByUserName(userName);
        if (userDTO != null) {
            return userDTO;
        }
        return null;
    }

    public UserDTO login(UserDTO userDTO) {
        UserDTO result = this.findByName(userDTO.getUserName());
        if (result != null)//用户名存在
        {
            if (result.getPassword().equals(MD5EncryptUtil.encryptPassword(userDTO.getPassword(), result.getUserId()))) {
                return result;
            } else {
                throw new UserPasswordException("用户密码错误！");
            }
        } else {
            throw new UserNotExistedException("用户未注册或者用户名输入错误！");
        }
    }

    public int register(UserDTO userDTO) {
        UserDTO result = this.findByName(userDTO.getUserName());
        if (result != null) {
            throw new UserIsExistedException("用户已经存在");
        } else {
            userDTO.setUserId(UUIDUtils.get32UUIDAsLowerCase());
            userDTO.setPassword(MD5EncryptUtil.encryptPassword(userDTO.getPassword(), userDTO.getUserId()));
            return userMapper.insertSelective(userDTO);
        }

    }

    public Page<UserDTO> pageUser(QueryUserCommand queryUserCommand) {
        int totalRecord = userMapper.getCountByCondition(queryUserCommand);
        int pageCode = queryUserCommand.getPage().getPageCode();
        Page<UserDTO> pu = new Page<UserDTO>(pageCode, totalRecord);
        List<UserDTO> datas = userMapper.pageByCondition((pageCode - 1) * pu.getPageSize(), pu.getPageSize(), queryUserCommand);
        for (UserDTO user : datas) {
            user.setPassword(null);
        }
        pu.setDatas(datas);
        return pu;
    }

    public HttpServletResponse getExcelBySupply(String supplierId, HttpServletResponse res) throws IOException {
        List<UserDTO> userDTOList = this.userMapper.selectUserByID(supplierId);
        /**
         * 以下为生成Excel操作
         */
        // 1.创建一个workbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        HSSFSheet sheet = wb.createSheet("用户表");
        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 4.创建单元格，设置值表头，设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("用户序号");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("用户姓名");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("密码");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("年龄");
        cell.setCellStyle(style);
/*
        cell = row.createCell(5);
        cell.setCellValue("数量");
        cell.setCellStyle(style);*/


        // 循环将数据写入Excel
        for (int i = 0; i < userDTOList.size(); i++) {
            row = sheet.createRow((int) i + 1);
            UserDTO users = userDTOList.get(i);
            // 创建单元格，设置值
            row.createCell(0).setCellValue(i + 1);
            row.createCell(1).setCellValue(users.getUserId());
            row.createCell(2).setCellValue(users.getUserName());
            row.createCell(3).setCellValue(users.getPassword());
            row.createCell(4).setCellValue(users.getAge());
        }
        // 1.创建一个workbook，对应一个Excel文件

        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        HSSFSheet sheet1 = wb.createSheet("用户表1");
        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
        HSSFRow row1 = sheet1.createRow((int) 0);
        // 4.创建单元格，设置值表头，设置表头居中
        HSSFCellStyle style1 = wb.createCellStyle();
        // 居中格式
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置表头
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("序号");
        cell1.setCellStyle(style);

        cell1 = row1.createCell(1);
        cell1.setCellValue("用户序号");
        cell1.setCellStyle(style1);

        cell1 = row1.createCell(2);
        cell1.setCellValue("用户姓名");
        cell1.setCellStyle(style1);

        cell1 = row1.createCell(3);
        cell1.setCellValue("密码");
        cell1.setCellStyle(style1);

        cell1 = row1.createCell(4);
        cell1.setCellValue("年龄");
        cell1.setCellStyle(style1);
/*
        cell = row.createCell(5);
        cell.setCellValue("数量");
        cell.setCellStyle(style);*/


        // 循环将数据写入Excel
        for (int i = 0; i < userDTOList.size(); i++) {
            row1 = sheet1.createRow((int) i + 1);
            UserDTO users = userDTOList.get(i);
            // 创建单元格，设置值
            row1.createCell(0).setCellValue(i + 1);
            row1.createCell(1).setCellValue(users.getUserId());
            row1.createCell(2).setCellValue(users.getUserName());
            row1.createCell(3).setCellValue(users.getPassword());
            row1.createCell(4).setCellValue(users.getAge());
        }
        String fileName = "用户表";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");
        res.setHeader("Content-Disposition", "attachment;filename="
                + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = res.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
        return res;
    }

    public HttpServletResponse getExcelBySupply1(HSSFWorkbook wb, String supplierId, HttpServletResponse res) throws IOException {
        List<UserDTO> userDTOList = this.userMapper.selectUserByID(supplierId);

        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        HSSFSheet sheet = wb.createSheet("用户名");
        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 4.创建单元格，设置值表头，设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("用户序号");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("用户姓名");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("密码");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("年龄");
        cell.setCellStyle(style);
/*
        cell = row.createCell(5);
        cell.setCellValue("数量");
        cell.setCellStyle(style);*/


        // 循环将数据写入Excel
        for (int i = 0; i < userDTOList.size(); i++) {
            row = sheet.createRow((int) i + 1);
            UserDTO users = userDTOList.get(i);
            // 创建单元格，设置值
            row.createCell(0).setCellValue(i + 1);
            row.createCell(1).setCellValue(users.getUserId());
            row.createCell(2).setCellValue(users.getUserName());
            row.createCell(3).setCellValue(users.getPassword());
            row.createCell(4).setCellValue(users.getAge());
        }

        String fileName = "用户表";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");
        res.setHeader("Content-Disposition", "attachment;filename="
                + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = res.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
        return res;
    }

    public List<String> usersListSave(String filePath, String userId) {
        errorNum = 0;
        errorList.clear();
        BufferedInputStream out = null;
        try {
            out = new BufferedInputStream(new FileInputStream(filePath));
            List<UserDTO> lists = toExcel(filePath, out, 1);
            if (errorNum > 0) {
                return errorList;
            } else if (lists.size() == 0) {
                errorList.add("上传数据为空!");
                return errorList;
            } else {
                int flag = this.userMapper.insertUsers(lists, userId);
            }
        } catch (IOException e1) {
            e1.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return errorList;
    }

    public List<String> ListSave(String filePath) {
        errorNum = 0;
        errorList.clear();
        BufferedInputStream out = null;
        try {
            out = new BufferedInputStream(new FileInputStream(filePath));
            List<DTowerDTO> lists = toExcel1(filePath, out, 1);
            if (errorNum > 0) {
                return errorList;
            } else if (lists.size() == 0) {
                errorList.add("上传数据为空!");
                return errorList;
            } else {
                int flag = this.dtowerMapper.insertDTowers(lists);
              /*  DTowerDTO test=new DTowerDTO();
                test.setId("1");
                this.dtowerMapper.insert(test);*/
            }
        } catch (IOException e1) {
            e1.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return errorList;
    }

    public List<String> ListSave2(String filePath) {
        errorNum = 0;
        errorList.clear();
        BufferedInputStream out = null;
        try {
            out = new BufferedInputStream(new FileInputStream(filePath));
            List<UserDTO> lists = toExcel2(filePath, out, 1);
            if (errorNum > 0) {
                return errorList;
            } else if (lists.size() == 0) {
                errorList.add("上传数据为空!");
                return errorList;
            } else {
                //int flag = this.userMapper.inseretDTowrs(lists);
              /*  DTowerDTO test=new DTowerDTO();
                test.setId("1");
                this.dtowerMapper.insert(test);*/
            }
        } catch (IOException e1) {
            e1.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return errorList;
    }

    public List<UserDTO> toExcel(String fileName, BufferedInputStream bis, int flag) throws IOException, InvalidFormatException {
        List<UserDTO> userdatas = new ArrayList<UserDTO>();
        Workbook wb = null;
        try {
            if (fileName.endsWith(".xls")) {
                wb = new HSSFWorkbook(bis);// Excel 2003
            } else if (fileName.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(bis);// Excel 2007
            } else {
                return null;
            }
            Sheet sheet = getSheetByNum(wb, 2);
            if (flag == 1)
                userdatas = getSheetDataList(sheet);
            else userdatas = getSheetDataList(sheet);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userdatas;
    }

    public Workbook getWorkBook(String fileName, BufferedInputStream bis) {
        Workbook wb = null;
        try {
            if (fileName.endsWith(".xls")) {
                wb = new HSSFWorkbook(bis);// Excel 2003
            } else if (fileName.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(bis);// Excel 2007
            } else {
                return null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public List<DTowerDTO> toExcel1(String fileName, BufferedInputStream bs, int flag) throws IOException, InvalidFormatException {
        List<DTowerDTO> dtowerdatas = new ArrayList<DTowerDTO>();

        try {
            Workbook wb = getWorkBook(fileName, bs);
            Sheet sheet = getSheetByNum(wb, 1);
            if (flag == 1)
                dtowerdatas = getSheetDataList1(sheet);
            else dtowerdatas = getSheetDataList1(sheet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dtowerdatas;
    }

    public List<UserDTO> toExcel2(String fileName, BufferedInputStream bs, int flag) throws IOException, InvalidFormatException {
        List<UserDTO> dtowerdatas = new ArrayList<UserDTO>();

        try {
            Workbook wb = getWorkBook(fileName, bs);
            Sheet sheet = getSheetByNum(wb, 2);
            if (flag == 1)
                dtowerdatas = getSheetDataList(sheet);
            else dtowerdatas = getSheetDataList(sheet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dtowerdatas;
    }


    public List<UserDTO> getSheetDataList(Sheet sheet) throws Exception {
        List<UserDTO> sheetdatas = new ArrayList<UserDTO>();
        //需要先合并单元格数据
        mergedRegion(sheet);
        int lastRowNum = getRowNum(sheet);
        String regex1 = "^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$";
        for (int i = 0; i < lastRowNum; i++) {
            if (errorNum > 100) {
                break;
            }
            Row row = sheet.getRow(i);
            UserDTO users = new UserDTO();
            List<String> list1 = new ArrayList<String>();
            if (row == null) {
                continue;
            } else {
                for (int j = 0; j < 7; j++) {
                    list1.add(getCellData(row.getCell(j)));
                }
                if (i == 0) {
                    /*if (!list1.get(0).equals("序号") || !list1.get(1).equals("用户序号") || !list1.get(2).equals("用户姓名") || !list1.get(3).equals("密码") || !
                            list1.get(4).equals("年龄")) {
                        errorList.add(list1.get(0) + "-3");
                        errorNum = errorNum + 1;
                        break;
                    }
                    continue;*/
                } else {
                    String a = list1.get(0);
                    users.setId(list1.get(0));
                    users.setUserId(list1.get(1));
                    users.setUserName(list1.get(2));
                    users.setPassword(list1.get(3));
                    users.setAge(list1.get(4));
                    sheetdatas.add(users);
                }
            }
        }
        return sheetdatas;
    }

    public List<DTowerDTO> getSheetDataList1(Sheet sheet) throws Exception {
        List<DTowerDTO> sheetdatas = new ArrayList<DTowerDTO>();
        //需要先合并单元格数据
        mergedRegion(sheet);
        int lastRowNum = getRowNum(sheet);
        String regex1 = "^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$";
        for (int i = 0; i < lastRowNum; i++) {
            if (errorNum > 100) {
                break;
            }
            Row row = sheet.getRow(i);
            DTowerDTO dtower = new DTowerDTO();
            List<String> list1 = new ArrayList<String>();
            if (row == null) {
                continue;
            } else {
                for (int j = 0; j < 6; j++) {

                    list1.add(getCellData(row.getCell(j)));
                }
                if (i == 0 || i == 1) {
                /*    if (!list1.get(0).equals("序号") || !list1.get(1).equals("用户序号") || !list1.get(2).equals("用户姓名") || !list1.get(3).equals("密码") || !
                            list1.get(4).equals("年龄")) {
                        errorList.add(list1.get(0) + "-3");
                        errorNum = errorNum + 1;
                        break;
                    }
                    continue;*/
                } else {

                    dtower.setId(list1.get(0));
                    dtower.setSbid(list1.get(1));
                    dtower.setDeviceName(list1.get(2));
                    // dtower.setRunNumber(null);
                    // dtower.setRelationId(null);
                    dtower.setRunNumber(list1.get(3));
                    dtower.setRelationId(list1.get(4));
                    dtower.setTowerProperty(list1.get(5));

                    sheetdatas.add(dtower);
                }
            }
        }
        return sheetdatas;
    }

    public static void mergedRegion(Sheet sheet) throws Exception {
        //合并的单元格数量
        int merged = sheet.getNumMergedRegions();
        //预读合并的单元格
        for (int i = 0; i < merged; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int y0 = range.getFirstRow();
            int x0 = range.getFirstColumn();
            int y1 = range.getLastRow();
            int x1 = range.getLastColumn();

            String value = getSheetCellValueWithRowIndexAndColIndex(sheet, y0, x0);

            for (int m = y0; m <= y1; m++) {
                for (int n = x0; n <= x1; n++) {
                    setCellValue(sheet, m, n, value);
                }
            }
        }
    }

    public static void setCellValue(Sheet sheet, int rowNum, int cellNum, String value) {
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(cellNum);
        if (cell == null) {
            row.createCell(cellNum).setCellValue(value);
        } else {
            cell.setCellValue(value);
        }
    }

    public static String getSheetCellValueWithRowIndexAndColIndex(Sheet sheet, int rowNum, int cellNum) throws Exception {
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(cellNum);
        return getCellData(cell);
    }

    /**
     * 获取单元中值(字符串类型)
     * @param cell
     * @return
     */
    public static String getCellData(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            try {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK://空白
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_NUMERIC: //数值型 0----日期类型也是数值型的一种
                        if (DateUtil.isCellDateFormatted(cell)) {
                            short format = cell.getCellStyle().getDataFormat();

                            if (yyyyMMddList.contains(format)) {
                                sFormat = new SimpleDateFormat("yyyy-MM-dd");
                            } else if (hhMMssList.contains(format)) {
                                sFormat = new SimpleDateFormat("HH:mm:ss");
                            }
                            Date date = cell.getDateCellValue();
                            cellValue = sFormat.format(date);
                        } else {
                            //cell.setCellType(cell.CELL_TYPE_STRING);
                            Double numberDate = new BigDecimal(cell.getNumericCellValue()).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue();
                            cellValue = numberDate + "";
                        }
                        break;
                    case Cell.CELL_TYPE_STRING: //字符串型 1
                        cellValue = replaceBlank(cell.getStringCellValue());


                        break;
                    case Cell.CELL_TYPE_FORMULA: //公式型 2
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cellValue = replaceBlank(cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_BOOLEAN: //布尔型 4
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR: //错误 5
                        cellValue = "!#REF!";
                        break;
                }
            } catch (Exception e) {
                System.out.println("读取Excel单元格数据出错：" + e.getMessage());
                return cellValue;
            }
        }

        return cellValue;
    }

    public static Sheet getSheetByNum(Workbook book, int number) throws Exception {
        return book.getSheetAt(number - 1);
    }

    public static String replaceBlank(String source) {
        String dest = "";
        if (source != null) {
            Pattern p = Pattern.compile("\"s*|t|r|n");
            Matcher m = p.matcher(source);
            dest = m.replaceAll("");
        }
        return dest;
    }

    public static int getRowNum(Sheet sheet) {
        return sheet.getLastRowNum() + 1;
    }

}